package DB;

import Model.*;

import java.sql.*;
import java.util.ArrayList;

public class DBVaregenre_Vare
{
	private Connection con;
	
	private static DBVaregenre_Vare instance = null;
    
    public static DBVaregenre_Vare getInstance()
    {
        if (instance == null)
        {
          instance = new DBVaregenre_Vare();
        }
        return instance;
    }
	
	public DBVaregenre_Vare()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Vare_Varegenre";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private Vare_Varegenre singleWhere(String wClause, boolean retrieveAssocation)
	{
		ResultSet results;
		Vare_Varegenre vgv = new Vare_Varegenre();
	    String query =  buildQuery(wClause);

		try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if (results.next())
	 		{
 				vgv = buildVare_Varegenre(results,retrieveAssocation);
                stmt.close();
			}
		}//end try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return vgv;
	}

	private void create(String wClause)
	{
	    String query="INSERT INTO Vare_Varegenre ";

		if (wClause.length()>0)
		{
			query=query+ wClause;
			
			Statement stmt;
			
			try
			{
				stmt = con.createStatement(); 
				stmt.executeUpdate(query); 
				con.commit(); 
				stmt.close();
			}
			catch (Exception e)
			{ 
				e.printStackTrace(); 
			}
		}
		else
		{
		}
	}
	
	public void createVare_Varegenre(Vare_Varegenre vgv)
	{
		String wClause = "VALUES " 
			+ "(" + vgv.getG() + ","
			+ "" + vgv.getV().getVareid() + ")";
	        create(wClause);
	}
	
	public void deleteVare_Varegenre(int vgid)
	{
		PreparedStatement prepstmt;
		try 
		{     
		  prepstmt = con.prepareStatement
		    ("DELETE FROM Vare_Varegenre WHERE usid = " + vgid + "");   
		  prepstmt.executeUpdate();          
		  con.commit();
		  prepstmt.close();
		}
		catch (Exception e) 
		{
		  e.printStackTrace();
		}
	} 
	
	
	public ArrayList<Vare_Varegenre> getAlleVare_Varegenre()
	{
		return miscWhere("");
	}
	
	private ArrayList<Vare_Varegenre> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList list = new ArrayList();	
		
		String query =  buildQuery(wClause);
  
  		try
  		{// read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
			while(results.next())
			{
				Vare_Varegenre vgvObj = new Vare_Varegenre();
				vgvObj = buildVare_Varegenre(results, true);	
                 list.add(vgvObj);
			}//end while
			
			stmt.close();

		}//slut try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	public Vare_Varegenre findVare_Varegenre(int usid, boolean retrieveAssocation) 
    {
    	String wClause = "  usid = " + Integer.toString(usid) + "";
    	return singleWhere(wClause,retrieveAssocation);
    }
	
	private Vare_Varegenre buildVare_Varegenre(ResultSet results, boolean rA)
    {   
		DBVaregenre dbVg = DBVaregenre.getInstance();
		DBVare dbVar = DBVare.getInstance();
		Vare_Varegenre vgv = new Vare_Varegenre();
		try
		{
			
			if(rA == true)
			{
				vgv.setV(dbVar.findVareId(results.getInt(1), true));
				vgv.setG(dbVg.findVaregenre(results.getInt(2), true));
				
			}
			if(rA == false)
			{
				vgv.setV(dbVar.findVareId(results.getInt(1), false));
				vgv.setG(dbVg.findVaregenre(results.getInt(2), false));
			}
		}
		catch(Exception e)
		{
			System.out.println("error in building the VGV object");
		}
		return vgv;
	}

	public ArrayList<Vare_Varegenre> findVaregenreViaVare(int vareid, boolean rA) 
	{
		String query = "SELECT * FROM Vare_Varegenre where vareid =" + vareid;
		ResultSet results;
		ArrayList<Vare_Varegenre> list = new ArrayList<Vare_Varegenre>();	
  
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
			while(results.next())
			{
				Vare_Varegenre vvgObj = new Vare_Varegenre();
				vvgObj = buildVare_Varegenre(results, rA);	
                list.add(vvgObj);
			}
			stmt.close();
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
}